﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using DTO;

namespace DAO
{
    public class PhanCongXeTaiXeDAO
    {
        public static List<int> Them(List<PhanCongXeTaiXeDTO> lstPcXeTaiXeDto, PhanCongDTO pcDto)
        {
            List<int> lstResult = new List<int>();

            //Tao va mo mot ket noi to co so du lieu
            using (OleDbConnection cnn = BoKetNoiCSDL.KetNoi())
            {
                //Tao doi tuong OleDbCommand tuong ung voi ket noi da mo
                using (OleDbCommand cmd = cnn.CreateCommand())
                {
                    //Khai bao mot transaction
                    OleDbTransaction transaction = cnn.BeginTransaction();
                    cmd.Transaction = transaction;
                    bool bInsertedPhanCong = false;
                    Int32 maPC = new Int32();
                    foreach (PhanCongXeTaiXeDTO pcXeTaiXeDto in lstPcXeTaiXeDto)
                    {
                        int result = 0;
                        try
                        {
                            if (!bInsertedPhanCong)
                            {
                                cmd.CommandText = String.Format("Insert into PHAN_CONG(NgayPhanCong, MaNVPC) values('{0}', '{1}')", pcDto.NgayPhanCong.ToShortDateString(), pcDto.NhanVienPhanCong);
                                int effect = cmd.ExecuteNonQuery();
                                if (effect != 0)
                                {
                                    bInsertedPhanCong = !bInsertedPhanCong;
                                    cmd.CommandText = "Select @@Identity";
                                    maPC = (int)cmd.ExecuteScalar();
                                }
                            }
                            string insertCmd = "Insert into PHAN_CONG_XE_TAI_XE(MaPC, BienSoXe, MaTaiXe, ThoiGianBatDau, ThoiGianKetThuc)";
                            insertCmd += "Values(?, ?, ?, ?, ?)";
                            cmd.CommandText = insertCmd;

                            
                            cmd.Parameters.Add("@MaPC", OleDbType.Integer);
                            cmd.Parameters.Add("@BienSoXe", OleDbType.VarChar);
                            cmd.Parameters.Add("@MaTaiXe", OleDbType.Integer);
                            cmd.Parameters.Add("@ThoiGianBatDau", OleDbType.Date);
                            cmd.Parameters.Add("@ThoiGianKetThuc", OleDbType.Date);


                            cmd.Parameters["@MaPC"].Value = maPC;
                            cmd.Parameters["@BienSoXe"].Value = pcXeTaiXeDto.BienSo;
                            cmd.Parameters["@MaTaiXe"].Value = pcXeTaiXeDto.MaTaiXe;
                            cmd.Parameters["@ThoiGianBatDau"].Value = pcXeTaiXeDto.ThoiGianBatDau;
                            cmd.Parameters["@ThoiGianKetThuc"].Value = pcXeTaiXeDto.ThoiGianKetThuc;

                            result = cmd.ExecuteNonQuery();
                            
                            lstResult.Add(result);
                            
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            lstResult.Add(result);
                            if (cnn.State == ConnectionState.Open)
                            {
                                cnn.Close();
                            }
                            throw ex;
                        }
                        
                    }
                    transaction.Commit();
                    cnn.Close();
                }
            }
            return lstResult;
        }

        public static int Them(PhanCongXeTaiXeDTO pcXeTaiXeDto, PhanCongDTO pcDto)
        {
            int result = 0;

            //Tao va mo mot ket noi to co so du lieu
            using (OleDbConnection cnn = BoKetNoiCSDL.KetNoi())
            {
                //Tao doi tuong OleDbCommand tuong ung voi ket noi da mo
                using (OleDbCommand cmd = cnn.CreateCommand())
                {
                    //Khai bao mot transaction
                    OleDbTransaction transaction = cnn.BeginTransaction();
                    cmd.Transaction = transaction;
                    try
                    {
                        cmd.CommandText = String.Format("Insert into PHAN_CONG(NgayPhanCong, MaNVPC) values('{0}', '{1}')", pcDto.NgayPhanCong.ToShortDateString(), pcDto.NhanVienPhanCong);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "Select @@Identity";
                        int maPC = (int)cmd.ExecuteScalar();
                        
                        pcXeTaiXeDto.MaPhanCong = maPC;

                        string insertCmd = "Insert into PHAN_CONG_XE_TAI_XE(MaPC, BienSoXe, MaTaiXe, ThoiGianBatDau, ThoiGianKetThuc)";
                        insertCmd += "Values(?, ?, ?, ?, ?)";
                        cmd.CommandText = insertCmd;

                        
                        cmd.Parameters.Add("@MaPC", OleDbType.Integer);
                        cmd.Parameters.Add("@BienSoXe", OleDbType.VarChar);
                        cmd.Parameters.Add("@MaTaiXe", OleDbType.Integer);
                        cmd.Parameters.Add("@ThoiGianBatDau", OleDbType.Date);
                        cmd.Parameters.Add("@ThoiGianKetThuc", OleDbType.Date);

                        
                        cmd.Parameters["@MaPC"].Value = pcXeTaiXeDto.MaPhanCong;
                        cmd.Parameters["@BienSoXe"].Value = pcXeTaiXeDto.BienSo;
                        cmd.Parameters["@MaTaiXe"].Value = pcXeTaiXeDto.MaTaiXe;
                        cmd.Parameters["@ThoiGianBatDau"].Value = pcXeTaiXeDto.ThoiGianBatDau;
                        cmd.Parameters["@ThoiGianKetThuc"].Value = pcXeTaiXeDto.ThoiGianKetThuc;

                        result = cmd.ExecuteNonQuery();
                        transaction.Commit();
                        cnn.Close();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        if (cnn.State == ConnectionState.Open)
                        {
                            cnn.Close();
                        }
                        throw ex;
                    }
                    finally
                    {
                        cnn.Dispose();
                    }
                }
            }
            return result;

        }
    }
}
